﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.UI;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections;
using MM.Common;

namespace MM.DBUtility
{
    public class BLSqlData
    {
        //将省份信息放在内存中
        public static List<ProvinceInfo> AllProvince { get; set; }
        static BLSqlData()
        {
            AllProvince = GetAllProvince();
        }

        /// <summary>
        /// 日志记录
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="Content"></param>
        /// <param name="LogType"></param>
        public static void InsertLogs(string UserID, string Content, string LogType)
        {
            try
            {
                string strSQL = "INSERT INTO T_SysLogs(UserID,Content,LogType) values(@UserID,@Content,@LogType)";
                SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.VarChar,50),
					new SqlParameter("@Content", SqlDbType.NVarChar,500),
                    new SqlParameter("@LogType", SqlDbType.NVarChar,50)};
                parameters[0].Value = UserID;
                parameters[1].Value = Content;
                parameters[2].Value = LogType;
                DbHelperSql.ExecuteSql(strSQL, parameters);
            }
            catch { }
        }

        /// <summary>
        /// 返回查询条件in
        /// </summary>
        /// <param name="strValue"></param>
        /// <returns></returns>
        public static string GetInSQL(string strValue)
        {
            string strResult = string.Empty;
            if (strValue.Contains(","))
            {
                string[] argstr = strValue.Split(',');
                for (int i = 0; i < argstr.Length; i++)
                {
                    strResult += "'" + argstr[i] + "',";
                }
                if (strResult.Contains(","))
                    strResult = strResult.Remove(strResult.LastIndexOf(","));
            }
            else strResult = "'" + strValue + "'";
            return strResult;
        }
        /// <summary>
        /// 取得省份信息
        /// </summary>
        /// <returns></returns>
        public static List<ProvinceInfo> GetAllProvince()
        {
            DataTable dt = DbHelperSql.Query("SELECT * FROM T_Province order by PinYinCode asc").Tables[0];
            List<ProvinceInfo> Pcoll = new List<ProvinceInfo>();
            ProvinceInfo Pitem;
            foreach (DataRow row in dt.Rows)
            {
                Pitem = new ProvinceInfo();
                Pitem.ProvinceID = row["ProvinceID"].ToString();
                Pitem.ProvinceName = row["ProvinceName"].ToString();
                Pitem.PinYinCode = row["PinYinCode"].ToString();
                Pcoll.Add(Pitem);
            }
            return Pcoll;
        }
        /// <summary>
        /// 根据快速省份下拉控件取得ProvinceID
        /// </summary>
        /// <param name="QuickName"></param>
        /// <returns></returns>
        public static string GetProvinceIDbyQuickName(string QuickName)
        {
            string strResult = string.Empty;
            try
            {
                if (!string.IsNullOrEmpty(QuickName))
                {
                    if (QuickName.Contains(" ") && QuickName.IndexOf(" ") == 1)
                        QuickName = QuickName.Remove(0, 2);
                    ProvinceInfo Pitem = AllProvince.First(o => o.ProvinceName.Equals(QuickName));
                    if (Pitem != null)
                        strResult = Pitem.ProvinceID;
                }
            }
            catch { }
            return strResult;
        }
        /// <summary>
        /// 根据ProvinceID取得省份和拼音组合名称
        /// </summary>
        /// <param name="ProvinceID"></param>
        /// <returns></returns>
        public static string GetNameByProvinceID(string ProvinceID)
        {
            string strResult = string.Empty;
            try
            {
                if (!string.IsNullOrEmpty(ProvinceID))
                {
                    ProvinceInfo Pitem = AllProvince.First(o => o.ProvinceID.Equals(ProvinceID));
                    if (Pitem != null)
                        strResult = Pitem.PinYinCode + " " + Pitem.ProvinceName;
                }
            }
            catch { }
            return strResult;
        }
        /// <summary>
        /// 根据快速省份下拉控件取得省份名称
        /// </summary>
        /// <param name="ProvinceID"></param>
        /// <returns></returns>
        public static string GetProvinceNamebyQuickName(string QuickName)
        {
            string strResult = string.Empty;
            try
            {
                if (!string.IsNullOrEmpty(QuickName))
                {
                    if (QuickName.Contains(" ") && QuickName.IndexOf(" ") == 1)
                        strResult = QuickName.Remove(0, 2);
                }
            }
            catch { }
            return strResult;
        }
        /// <summary>
        /// 区域表中，区域号+运营商是否存在
        /// </summary>
        /// <param name="strAreaNO"></param>
        /// <returns></returns>
        public static bool ExistAreaNO(string strAreaNO, string strONO)
        {
            return DbHelperSql.Exists(string.Format("SELECT ID FROM T_Area WHERE AreaNO='{0}' AND ONO='{1}'", strAreaNO, strONO));
        }
        /// <summary>
        /// 通道表中，通道编号是否存在
        /// </summary>
        /// <param name="strNO"></param>
        /// <returns></returns>
        public static bool ExistChannelNO(string strNO)
        {
            return DbHelperSql.Exists(string.Format("SELECT ID FROM T_Channel WHERE ChannelNO='{0}'", strNO));
        }
        /// <summary>
        /// 运行商表中，运行商编号是否存在
        /// </summary>
        /// <param name="strNO"></param>
        /// <returns></returns>
        public static bool ExistODEPNO(string strNO)
        {
            return DbHelperSql.Exists(string.Format("SELECT ID FROM T_ODEP WHERE ONO='{0}'", strNO));
        }
        /// <summary>
        /// 通道商表中，通道商编号是否存在
        /// </summary>
        /// <param name="strNO"></param>
        /// <returns></returns>
        public static bool ExistSPbusiNO(string strNO)
        {
            return DbHelperSql.Exists(string.Format("SELECT 1 FROM T_SPbusi WHERE SNO='{0}'", strNO));
        }
        /// <summary>
        /// 客户表中，客户编号是否存在
        /// </summary>
        /// <param name="strNO"></param>
        /// <returns></returns>
        public static bool ExistCustomerNO(string strNO)
        {
            return DbHelperSql.Exists(string.Format("SELECT ID FROM T_Customers WHERE CNO='{0}'", strNO));
        }
        /// <summary>
        /// 规则表中，区号，运营商，客户编号对应的规则是否存在
        /// </summary>
        /// <param name="strArea"></param>
        /// <param name="strOp"></param>
        /// <param name="strId"></param>
        /// <returns></returns>
        public static bool ExistRules(string strProvinceID, string strOp, string strId)
        {
            return DbHelperSql.Exists(string.Format("SELECT ID FROM T_Rules WHERE ProvinceID={0} AND ONO='{1}' AND CNO='{2}'", strProvinceID, strOp, strId));
        }
    }

    public class ProvinceInfo
    {
        private string _ProvinceID;
        public string ProvinceID
        {
            get { return _ProvinceID; }
            set { _ProvinceID = value; }
        }

        private string _ProvinceName;
        public string ProvinceName
        {
            get { return _ProvinceName; }
            set { _ProvinceName = value; }
        }

        private string _PinYinCode;
        public string PinYinCode
        {
            get { return _PinYinCode; }
            set { _PinYinCode = value; }
        }
    }
}
